////////////////////////////////////////////////////////////////////////////////
//
//  FLEX USER GROUP HOKURIKU
//  Copyright 2009 Flex User Group Hokuriku
//  All Rights Reserved.
// 
//  NOTICE: Flex User Group Hokuriku permits you to use, 
//  modify, and distribute this file 
//  in accordance with the terms of the license agreement accompanying it.
//
////////////////////////////////////////////////////////////////////////////////
package net.fxug.hokuriku.chocolat.dao.contents
{
    import flash.errors.IllegalOperationError;

	/**
	 * SQLQuery定義クラス. 
	 * @author hikaruworld
	 */	
    public class SQLQuery
    {
		/**
		 * デフォルトコンストラクタは使用禁止
		 */		
		[Deprecated("インスタンス化禁止")]
        public function SQLQuery()
        {
            throw new IllegalOperationError("このクラスは定数クラスです。インスタンス化は許可されていません。");
        }
		//---------------------------------------------------------------------//
		// CREATE系
		// FIXME ;でCREATE文をつないでも連続で実行できないので別途定義している。
		//---------------------------------------------------------------------//
        /** 初期DB:SESSION */
        public static const CREATE_TABLE_SESSION:String =
			"CREATE TABLE IF NOT EXISTS SESSION" +
			"	(" +
			"		SESSION_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
			"		START_DATETIME DATE NOT NULL," +
			"		TITLE TEXT," +
			"		SESSION_TAG TEXT," +
			"		ATND_ID INTEGER" +
			"	); ";
		/** 初期DB:VOTE */
		public static const CREATE_TABLE_VOTE:String = 
			"CREATE TABLE IF NOT EXISTS VOTE" +
			"	(" +
			"		SESSION_ID INTEGER NOT NULL," +
			"		USER_ID TEXT," +
			"		ROUTE TEXT," +
			"		DATETIME DATE NOT NULL," +
			"		TWEET TEXT," +
			"		PROTOCOL BOOLEAN " +
			"	); ";
		//---------------------------------------------------------------------//
		// セッション系
		//---------------------------------------------------------------------//
		/** セッション情報を取得する */
		public static const SELECT_SESSION:String =
			"SELECT " +
			"	SESSION_ID as sessionId," +
			"	START_DATETIME as startDatetime," +
			"	TITLE as title," +
			"	SESSION_TAG as sessionTag," +
			"	ATND_ID as atndId " +
			"	FROM" +
			"		SESSION;";
		/** セッション情報を登録する。 */
		public static const INSERT_SESSION:String =
			"INSERT INTO SESSION (" +
//			"	SESSION_ID," +		SESSION_IDはオートインクリメントする。
			"	START_DATETIME," +
			"	TITLE," +
			"	SESSION_TAG," +
			"	ATND_ID " +
			"	) values (" +
//			"		:sessionId," +
			"		:startDatetime," +
			"		:title," +
			"		:sessionTag," +
			"		:atndId" +
			"	);";
		//---------------------------------------------------------------------//
		// 投票テーブル関連
		//---------------------------------------------------------------------//
		/** 投票の取得 */
		public static const SELECT_VOTE:String =
			"SELECT " +
			"	SESSION_ID as sessionId," +
			"	USER_ID as userId," +
			"	ROUTE as route," +
			"	DATETIME as datetime," +
			"	PROTOCOL as protocol," +
			"	TWEET as tweet " +
			"	FROM" +
			"		VOTE;";
		/** セッションID指定の投票の取得 */
		public static const SELECT_VOTE_BY_SESSION_ID:String =
			"SELECT " +
			"	USER_ID as userId, " +
			"	ROUTE as route, " +
			"	SESSION_ID as sessionId," +
			"	DATETIME as datetime," +
			"	PROTOCOL as protocol," +
			"	TWEET as tweet " +
			"	FROM" +
			"		VOTE " +
			"	WHERE" +
			"		SESSION_ID = :sessionId";
        /** 投票の登録 */
        public static const INSERT_VOTE:String =
			"INSERT INTO VOTE (" +
			"	SESSION_ID," +
			"	USER_ID, " +
			"	ROUTE, " +
			"	DATETIME," +
			"	TWEET," +
			"	PROTOCOL " +
			"	) values (" +
			"		:sessionId," +
			"		:userId," +
			"		:route, " +
			"		:datetime," +
			"		:tweet," +
			"		:protocol " +
			"	);";
    }
}